class Sideffect < ActiveRecord::Base
  has_and_belongs_to_many :ratings
  def self.get_top_sideffects
    sql = <<-SQL
      select s.name name, t.cnt cnt
      from sideffects s,
      (select rs.sideffect_id sideffect_id, count(distinct rs.rating_id) cnt
      from ratings_sideffects rs 
      group by rs.sideffect_id
      order by cnt desc
      limit 20
    ) t
    where t.sideffect_id = s.id
    SQL
    connection.select_all(sql)
  end
  def get_category_total(category_id, drug_list, sex, age, time_taken)
    drugs_filter = drug_list.collect {|d| "or r.drug_id = #{d.to_i}" unless d.to_i == 0} .join(" ")
    drugs_filter = drugs_filter[3..-1]
    drugs_filter = "r.id = 0" if drugs_filter.blank?
    sex_filter = case sex
                 when "F"
                   "r.sex = 'F'"
                 when "M"
                   "r.sex = 'M'"
                 else
                   "1"
                 end
    age_filter = case age
                 when "18"
                   "r.age <= 18"
                 when "45"
                   "r.age > 18 and r.age <= 45"
                 when "60"
                   "r.age > 45 and r.age <= 60"
                 when "inf"
                   "r.age > 60"
                 else
                   "1"
                 end
    time_taken_filter = case time_taken 
                        when "week"
                          "r.days_taken <= 7"
                        when "month"
                          "r.days_taken > 7 and r.days_taken <= 30"
                        when "season"
                          "r.days_taken > 30 and r.days_taken <= 90"
                        when "half_year"
                          "r.days_taken > 90 and r.days_taken <= 180"
                        when "year"
                          "r.days_taken > 180 and r.days_taken <= 365"
                        when "inf"
                          "r.days_taken > 365"
                        else
                          "1"
                        end
    sql = <<-SQL
      select count(distinct r.id) cnt
      from ratings r, ratings_sideffects rs, categories_drugs cd
      where
      cd.drug_id = r.drug_id
      and r.id = rs.rating_id
      and rs.sideffect_id = #{self[:id]}
      and #{sex_filter}
      and #{age_filter}
      and #{time_taken_filter}
      and (#{drugs_filter})
    SQL
    connection.select_all(sql)[0]["cnt"].to_i
  end
end
